const Database = require('better-sqlite3');

const db = new Database('./data/delivery-management.db');

try {
  console.log('=== 增强实例表结构：添加属性表字段 ===');
  
  // 1. 创建字段说明表
  console.log('1. 创建字段说明表...');
  
  const createFieldMappingTableSQL = `
    CREATE TABLE IF NOT EXISTS instance_field_mappings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      instance_table_name TEXT NOT NULL,
      field_name TEXT NOT NULL,
      source_table TEXT NOT NULL,
      source_table_type TEXT NOT NULL,
      field_type TEXT NOT NULL,
      is_required INTEGER DEFAULT 0,
      description TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      UNIQUE(instance_table_name, field_name)
    )
  `;
  
  db.prepare(createFieldMappingTableSQL).run();
  console.log('✓ 字段说明表 instance_field_mappings 创建成功');
  
  // 2. 获取所有对象类的属性表配置
  console.log('\\n2. 分析对象类的属性表配置...');
  
  const classifications = db.prepare(`
    SELECT 
      classification_name,
      english_name,
      specialty,
      naming_table,
      position_table,
      base_params_table,
      COUNT(*) as mapping_count
    FROM device_type_mappings
    WHERE english_name IS NOT NULL
    GROUP BY classification_name, specialty
    ORDER BY specialty, classification_name
  `).all();
  
  console.log(`找到 ${classifications.length} 个唯一对象类需要处理`);
  
  // 3. 专业前缀映射
  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical',
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };
  
  // 4. 处理每个对象类
  let processedCount = 0;
  
  for (const classification of classifications) {
    const specialtyPrefix = specialtyPrefixes[classification.specialty];
    if (!specialtyPrefix) {
      console.error(`未知专业: ${classification.specialty}`);
      continue;
    }
    
    const instanceTableName = `device_instances_${specialtyPrefix}_${classification.english_name}`;
    
    console.log(`\\n处理对象类: ${classification.classification_name} (${instanceTableName})`);
    
    // 检查实例表是否存在
    const tableExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name=?
    `).get(instanceTableName);
    
    if (!tableExists) {
      console.log(`  ✗ 实例表不存在: ${instanceTableName}`);
      continue;
    }
    
    // 获取三个属性表的字段信息
    const attributeTables = [
      { name: classification.naming_table, type: 'naming' },
      { name: classification.position_table, type: 'position' },
      { name: classification.base_params_table, type: 'base_params' }
    ].filter(table => table.name);
    
    const allNewFields = [];
    const fieldMappings = [];
    
    // 处理每个属性表
    for (const attrTable of attributeTables) {
      console.log(`  处理${attrTable.type}表: ${attrTable.name}`);
      
      // 检查属性表是否存在
      const attrTableExists = db.prepare(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
      `).get(attrTable.name);
      
      if (!attrTableExists) {
        console.log(`    ✗ 属性表不存在: ${attrTable.name}`);
        continue;
      }
      
      // 获取属性表的字段结构
      const attrFields = db.prepare(`PRAGMA table_info([${attrTable.name}])`).all();
      
      // 过滤掉系统字段
      const dataFields = attrFields.filter(field => 
        !['id', 'created_at', 'updated_at'].includes(field.name)
      );
      
      console.log(`    找到 ${dataFields.length} 个数据字段`);
      
      // 添加字段到实例表
      for (const field of dataFields) {
        const fieldName = `${attrTable.type}_${field.name}`;
        const fieldType = field.type || 'TEXT';
        
        allNewFields.push({
          name: fieldName,
          type: fieldType,
          sourceTable: attrTable.name,
          sourceType: attrTable.type,
          originalName: field.name
        });
        
        // 记录字段映射
        fieldMappings.push({
          instanceTableName,
          fieldName,
          sourceTable: attrTable.name,
          sourceTableType: attrTable.type,
          fieldType,
          originalName: field.name
        });
      }
    }
    
    console.log(`  总共需要添加 ${allNewFields.length} 个字段`);
    
    // 5. 添加字段到实例表
    const currentFields = db.prepare(`PRAGMA table_info([${instanceTableName}])`).all();
    const currentFieldNames = new Set(currentFields.map(f => f.name));
    
    let addedFields = 0;
    
    for (const newField of allNewFields) {
      if (!currentFieldNames.has(newField.name)) {
        try {
          const alterSQL = `ALTER TABLE [${instanceTableName}] ADD COLUMN [${newField.name}] ${newField.type}`;
          db.prepare(alterSQL).run();
          addedFields++;
          console.log(`    ✓ 添加字段: ${newField.name}`);
        } catch (error) {
          console.error(`    ✗ 添加字段失败 ${newField.name}:`, error.message);
        }
      }
    }
    
    console.log(`  ✓ 成功添加 ${addedFields} 个字段`);
    
    // 6. 保存字段映射到说明表
    const insertMappingSQL = `
      INSERT OR REPLACE INTO instance_field_mappings 
      (instance_table_name, field_name, source_table, source_table_type, field_type, description)
      VALUES (?, ?, ?, ?, ?, ?)
    `;
    
    const insertStmt = db.prepare(insertMappingSQL);
    
    for (const mapping of fieldMappings) {
      const description = `来自${mapping.sourceTableType}表(${mapping.sourceTable})的字段${mapping.originalName}`;
      
      try {
        insertStmt.run(
          mapping.instanceTableName,
          mapping.fieldName,
          mapping.sourceTable,
          mapping.sourceTableType,
          mapping.fieldType,
          description
        );
      } catch (error) {
        console.error(`    ✗ 保存字段映射失败:`, error.message);
      }
    }
    
    console.log(`  ✓ 保存了 ${fieldMappings.length} 个字段映射`);
    processedCount++;
  }
  
  // 7. 验证结果
  console.log('\\n3. 验证处理结果...');
  console.log(`成功处理 ${processedCount} 个对象类`);
  
  // 检查字段说明表的记录数
  const mappingCount = db.prepare('SELECT COUNT(*) as count FROM instance_field_mappings').get();
  console.log(`字段说明表中有 ${mappingCount.count} 条映射记录`);
  
  // 示例：查看冷却水喷淋装置的字段增强结果
  console.log('\\n4. 示例验证：冷却水喷淋装置');
  
  const exampleTableName = 'device_instances_piping_cooling_water_spray_system';
  const exampleFields = db.prepare(`PRAGMA table_info([${exampleTableName}])`).all();
  
  console.log(`${exampleTableName} 表结构:`);
  console.log(`- 总字段数: ${exampleFields.length}`);
  
  const baseFields = exampleFields.filter(f => 
    ['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f.name)
  );
  const attrFields = exampleFields.filter(f => 
    !['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f.name)
  );
  
  console.log(`- 基础字段: ${baseFields.length} 个`);
  console.log(`- 属性字段: ${attrFields.length} 个`);
  
  if (attrFields.length > 0) {
    console.log('属性字段示例:');
    attrFields.slice(0, 5).forEach(field => {
      console.log(`  - ${field.name} (${field.type})`);
    });
    if (attrFields.length > 5) {
      console.log(`  ... 还有 ${attrFields.length - 5} 个字段`);
    }
  }
  
  // 查看该表的字段映射
  const exampleMappings = db.prepare(`
    SELECT source_table_type, COUNT(*) as count
    FROM instance_field_mappings 
    WHERE instance_table_name = ?
    GROUP BY source_table_type
  `).all(exampleTableName);
  
  console.log('字段来源统计:');
  exampleMappings.forEach(mapping => {
    console.log(`  - ${mapping.source_table_type}: ${mapping.count} 个字段`);
  });
  
  console.log('\\n=== 增强完成 ===');
  console.log('✅ 实例表结构已增强，包含所有属性表字段');
  console.log('✅ 字段说明表已创建，记录字段来源信息');
  console.log('✅ 为属性数据提供了统一的存储位置');

} catch (error) {
  console.error('增强实例表失败:', error);
} finally {
  db.close();
}